/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.dtn.wsn.dao;

import com.dtn.wsn.database.ConnectionPool;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Data Access object for all the tables in the database
 * includes drop all tables, create the tables
 * @author hao
 */
public class TableManager {

    private ConnectionPool connectionPool;

    public TableManager(ConnectionPool connectionPool) {
        this.connectionPool = connectionPool;
    }
    
    /**
     * reinit the tables 
     * @param tableName 
     */
    
    public void reInitTable(String tableName){
         String sql = "drop table " + tableName;
        try {
            Connection cn = connectionPool.getConnection();
            Statement stmt = cn.createStatement();
            
            try {
                stmt.executeUpdate(sql);
                System.out.println("Existing table deleted.");
                
            } catch (Exception e) {
                System.out.println(e.getMessage());
                System.out.println(
                        "No existing table to delete.");
            }
            
            //create
            if(tableName.equals("gateway")){
                sql = "CREATE TABLE `gateway` (`gateway_id` INT  NOT NULL AUTO_INCREMENT," +
                        " `gateway_name` VARCHAR(20) ,`gateway_mac_addr` VARCHAR(100) ,`location` VARCHAR(100),"+
                        " PRIMARY KEY (`gateway_id`));";
            }else if(tableName.equals("sunspot")){
                sql = "CREATE TABLE  `sunspot` (`spot_id` int  NOT NULL AUTO_INCREMENT,`spotname` VARCHAR(20) ,"+
                    "  `spot_mac_addr` VARCHAR(100) ,`location` VARCHAR(100) ,PRIMARY KEY (`spot_id`));";
            
            }else if(tableName.equals("data")){
                sql = "CREATE TABLE `data` (`sensing_id` int  NOT NULL AUTO_INCREMENT,`spot_id` int ,"
                    +"  `timestamp` VARCHAR(40) ,`light` int ,`temperature` double ,`battery` double ,PRIMARY KEY (`sensing_id`));";
                
            }else if(tableName.equals("collecthistory")){
                sql = "CREATE TABLE  `collecthistory` (`id` int  NOT NULL AUTO_INCREMENT,`timestamp` varchar(40) ,"
                    +"  `port` int ,`duration` int ,`status` varchar(100) ,PRIMARY KEY (`id`))";

            }
            stmt.executeUpdate(sql);
            
            
            connectionPool.freeConnection(cn);
        } catch (SQLException e) {
            System.out.println("insert student record error :" + e);
        }
    
    
    }
    
    /**
     * init all the tables
     * 
     */
    public void initAllTable() {
        String sql = "drop table gateway,data,sunspot,collecthistory";
        try {
            Connection cn = connectionPool.getConnection();
            Statement stmt = cn.createStatement();
            
            try {
                stmt.executeUpdate(sql);
                System.out.println("Existing table deleted.");
                
            } catch (Exception e) {
                System.out.println(e.getMessage());
                System.out.println(
                        "No existing table to delete.");
            }
            
            //create
            sql = "CREATE TABLE `gateway` (`gateway_id` INT  NOT NULL AUTO_INCREMENT," +
                    " `gateway_name` VARCHAR(20) ,`gateway_mac_addr` VARCHAR(100) ,`location` VARCHAR(100),"+
                    " PRIMARY KEY (`gateway_id`));";
            stmt.executeUpdate(sql);
            sql = "CREATE TABLE  `sunspot` (`spot_id` int  NOT NULL AUTO_INCREMENT,`spotname` VARCHAR(20) ,"+
                    "  `spot_mac_addr` VARCHAR(100) ,`location` VARCHAR(100) ,PRIMARY KEY (`spot_id`));";
            stmt.executeUpdate(sql);
            sql = "CREATE TABLE `data` (`sensing_id` int  NOT NULL AUTO_INCREMENT,`spot_id` int ,"
                    +"  `timestamp` VARCHAR(40) ,`light` int ,`temperature` double ,`battery` double ,PRIMARY KEY (`sensing_id`));";
            stmt.executeUpdate(sql);
            sql = "CREATE TABLE  `collecthistory` (`id` int  NOT NULL AUTO_INCREMENT,`timestamp` varchar(40) ,"
                    +"  `port` int ,`duration` int ,`status` varchar(100) ,PRIMARY KEY (`id`))";

            stmt.executeUpdate(sql);
            
            
            connectionPool.freeConnection(cn);
        } catch (SQLException e) {
            System.out.println("insert student record error :" + e);
        }
    }
    
    
}
